package net.skyedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.skyedu.bean.Detail;
import net.skyedu.bean.ShowUser;
import net.skyedu.bean.SumLate;
import net.skyedu.utils.DateUtil;

public class AjaxDao extends DAO{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private DateUtil du = null;
	
	private void conn(StringBuffer sql) throws ClassNotFoundException,
	SQLException {
		conn=this.getConn();
		pstmt=conn.prepareStatement(sql.toString());
		rs=pstmt.executeQuery();
	}
	/**
	 * 
	 * @return
	 */
	public List<ShowUser> showMyAttend(int startIndex,int pagesize,int type, int userId, String sta){
		List<ShowUser> list = new ArrayList<ShowUser>();
		du= new DateUtil();
		final Map<String, String> map  = du.findThisMonth();
		final Map<String, String> map1 = du.findLastMonth();
		final Map<String, String> map2 = du.findThreeMonthsAgo();
		try{
			StringBuffer sql=new StringBuffer("select u.USERID,u.Name,d.DEPTNAME,u.SubDept,a.* from Attendance a,USERINFO u,DEPARTMENTS d " +
					"where a.att_user_id=u.USERID " +
					"and u.DEFAULTDEPTID=d.DEPTID " +
					"and u.DEFAULTDEPTID not in (1,-1) " +
					"and u.status=1 " +
					"and u.USERID="+ userId +" ");
			
			if (sta.equals("1")){
				  sql.append("and a.att_date between '" + map.get("beginTime") + "' and '" + map.get("endTime") + "' ");
			}
			if(sta.equals("2")){
				sql.append("and a.att_date between '" + map1.get("prevMonthFD") + "' and '" + map1.get("prevMonthPD") + "' ");
			}
			if(sta.equals("3")){
				sql.append("and a.att_date between '" + map2.get("threeMonthsAgoDate") + "' and '" + map2.get("nowDate") + "' ");
			}
			sql.append("order by a.att_date desc");
			System.out.println(sql);
			conn(sql);
			if(type != 0){
				int i = 0;
				if(rs != null){
					while(rs.next()){
						if((i > (startIndex-1)) && (i < (startIndex + pagesize))){
							ShowUser obj = new ShowUser();
							obj.setAttid(rs.getInt("att_id"));
							obj.setUserId(rs.getInt("USERID"));
							obj.setUsername(rs.getString("Name"));
							obj.setDeptname(rs.getString("DEPTNAME"));
							obj.setSubdept(rs.getString("SubDept"));
							obj.setAttdate(rs.getString("att_date"));
							obj.setAttweek(rs.getInt("att_week"));
							obj.setAttworktime(rs.getString("att_worktime"));
							obj.setAttclosingtime(rs.getString("att_closingtime"));
							obj.setAttstate(rs.getInt("att_state"));
							obj.setAttlatetime(rs.getInt("att_latetime"));
							obj.setAttearlytime(rs.getInt("att_earlytime"));
							obj.setAttconfirm(rs.getInt("att_confirm"));
							obj.setAttdesc(rs.getString("att_desc"));
							list.add(obj);
						}
						i++;
					}
				}
			}else{
				if(rs != null){
					while(rs.next()){
						ShowUser obj = new ShowUser();
						obj.setAttid(rs.getInt("att_id"));
						list.add(obj);
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	//获取迟到、早退总分钟
	public SumLate getSum(int userId,String code){
		SumLate obj = null;
		du= new DateUtil();
		final Map<String, String> map  = du.findThisMonth();
		final Map<String, String> map1 = du.findLastMonth();
		final Map<String, String> map2 = du.findThreeMonthsAgo();
		try{
			StringBuffer sql=new StringBuffer("select sum(att_latetime) la,sum(att_earlytime) ea from Attendance where att_user_id = "+ userId +" ");
			String sqls="";
			if(code.equals("1")){
				sqls="and att_date between '" + map.get("beginTime") + "' and '" + map.get("endTime") + "' ";
				sql.append(sqls);
			}
			if(code.equals("2")){
				sqls="and att_date between '" + map1.get("prevMonthFD") + "' and '" + map1.get("prevMonthPD") + "' ";
				sql.append(sqls);
			}
			if(code.equals("3")){
				sqls="and att_date between '" + map2.get("threeMonthsAgoDate") + "' and '" + map2.get("nowDate") + "' ";
				sql.append(sqls);
			}
			conn(sql);
			while(rs.next()){
				obj = new SumLate();
				obj.setLateSum(rs.getInt("la"));
				obj.setEarlySum(rs.getInt("ea"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return obj;
	}
	/**
	 * 我的考勤里查询打卡记录
	 * @param userId
	 * @param dt
	 * @return
	 */
	public List<Detail> getClockIn(int attId){
		List<Detail> list = new ArrayList<Detail>();
		try{
			StringBuffer sql=new StringBuffer("select distinct c.CheckTime,s.schName " +
					"from v_CheckInOut c,school s " +
					"where c.SENSORID=s.schId " +
					"and c.CheckDate=(select a.att_date from Attendance a where att_id="+attId+") " +
					"and USERID=(select a.att_user_id from Attendance a where att_id="+attId+") " +
					"order by c.CheckTime");
			System.out.println(sql);
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				Detail detail = new Detail();
				detail.setDatetime(rs.getString(1));
				detail.setSchName(rs.getString(2));
				list.add(detail);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 按考勤ID查询日期
	 * @param attId
	 * @return
	 */
	public String getDate(int attId){
		String str = "";
		try{
			StringBuffer sql=new StringBuffer("select a.att_date from Attendance a where att_id="+attId+"");
			
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				str = rs.getString(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return str;
	}
}
